package com.siqi.hr.system.custom.db.builder;

import com.siqi.hr.system.common.authority.Sysdata;
import com.siqi.hr.system.common.sql.RelatedConfig;
import com.siqi.hr.system.common.sql.RelatedTable;
import com.siqi.hr.system.custom.db.builder.intf.DbCreater;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.Assert;

import java.lang.reflect.Constructor;

/**
 * 如果是添加引用字段则添加主实体与引用实体关系表
 * @author    xiaogq
 * @data      2016-08-30
 */
public class RelatedAlter {

    private RelatedConfig relatedRecord;

    public RelatedAlter(RelatedConfig relatedRecord) {
        super();
        this.relatedRecord = relatedRecord;
    }

    public enum RelatedAlterType{
        //添加相关项
        related_add(RelatedAdd.class),
        //删除相关项
        related_removed(RelatedRemoved.class);

        private RelatedAlterType(Class<? extends DbCreater> cc){
            this.correspondingClass = cc;
        }

        private Class<? extends DbCreater> correspondingClass;
        public Class<? extends DbCreater> getDbCreater(){
            return correspondingClass;
        }
    }

    public DbCreater getDbCreater( RelatedAlterType type ){
        DbCreater creater = null;
        try {
            Class<? extends DbCreater> _c = type.getDbCreater();
            Constructor<?> con = _c.getDeclaredConstructors()[0];
            creater = (DbCreater)con.newInstance(this);
        } catch (Exception e) {}
        return creater;
    }

    public abstract class RelatedAlterAffluxJdbc implements DbCreater{

        public int create(JdbcTemplate jdbcTemplate){
            Assert.notNull(jdbcTemplate, "JdbcTemplate未正确的注入");
            this.jdbcTemplate = jdbcTemplate;
            excuteTableAlter();
            return 0;
        }
        // 后续处理
        public abstract void excuteTableAlter();

        // 	引入JDBC对象
        protected JdbcTemplate jdbcTemplate;
    }

    /**
     *  添加实体与实体自己关系表
     */
    public class RelatedAdd extends RelatedAlterAffluxJdbc{
        @Override
        public void excuteTableAlter() {
            // 添加记录信息
            addRecord();
            // 创建相关项表
            createRelatedTable();
        }

        public void rollbackDbException() {
            // 删除表
            jdbcTemplate.execute(" drop table  if exists " + relatedRecord.getRelatedTableName());
        }

        /**
         * 添加相关项表记录
         */
        private void addRecord(){
            StringBuffer sqlrc = new StringBuffer("select count(*) from sys_related_config where table_name = ?");
            if(relatedRecord.getSerial()==null || relatedRecord.getSerial() == 1){
                sqlrc.append(" and (serial = 1 or serial is null) ");
            } else {
                sqlrc.append(" and serial = "+relatedRecord.getSerial());
            }
            //根据相关项关系表名查询相关项关系表是否注册
            int i = jdbcTemplate.queryForObject(sqlrc.toString(),Integer.class,relatedRecord.getRelatedTableName());
            if(i == 0){//如果相关项关系表未注册则添加相关关系
                //添加相关项数据
                if(relatedRecord.getSerial() == null){
                    relatedRecord.setSerial(1);
                }
                //添加相关项关系记录
                String sql = " insert into sys_related_config (table_name, host_id, related_id, serial) values (?,?,?,?) ";
                jdbcTemplate.update(sql,new Object [] {relatedRecord.getRelatedTableName(),relatedRecord.parse().getHostTable().getEntityId(),relatedRecord.parse().getRelatedTable().getEntityId(),relatedRecord.getSerial()});
            }
        }

        /**
         * 创建主实体与相关实体之间的关系表
         */
        private void createRelatedTable(){
            final StringBuilder sql = new StringBuilder( " create table if not exists " ).append( relatedRecord.getRelatedTableName());
            sql.append("(").append(" `id` int(11) NOT NULL AUTO_INCREMENT,");
            sql.append(" host_record_id varchar(20) not null, related_record_id varchar(20) not null, serial int(10) DEFAULT '1', ");
            sql.append(" CONSTRAINT FOREIGN KEY (host_record_id) REFERENCES ");
            sql.append( relatedRecord.parse().getHostTable().getName() ).append( "(id) ON DELETE CASCADE ,");
            sql.append(" CONSTRAINT FOREIGN KEY (related_record_id) REFERENCES ");
            sql.append( relatedRecord.parse().getRelatedTable().getName() ).append( "(id) ON DELETE CASCADE ,");
            sql.append("primary key( id )");
            sql.append(")ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin");
            //System.out.println("create z_x_y sql ====="+sql.toString());
            jdbcTemplate.execute(sql.toString());
        }

    }

    /**
     *  删除相关项
     */
    public class RelatedRemoved extends RelatedAlterAffluxJdbc{
        @Override
        public void excuteTableAlter() {
            //检查是否有相关项
            if( existTable() ){
                // 删除相关项关系注册表，相关项操作表，相关关系表中的记录
                deleteRecord();
                // 删除相关项关系表(也就是删除z_x_y)
                deleteTable();
            }
        }

        /**
         * 检查是否有相关项表
         * @author xiagqo  2016-09-02
         * @return
         */
        private boolean existTable(){
            Integer hostId = relatedRecord.parse().getHostTable().getEntityId();
            Integer relatedId = relatedRecord.parse().getRelatedTable().getEntityId();
            String zTable = RelatedTable.RELATED_HEAD+hostId+"_"+relatedId;
            final String sql = "select count(0) from information_schema.`TABLES` where TABLE_SCHEMA = '" + Sysdata.getInstance().getDatabase()+"' and TABLE_NAME = '"+zTable+"'";
            int count = jdbcTemplate.queryForObject(sql,Integer.class);
            return count > 0;
        }

        /**
         * 删除相关项表对应记录
         * @author xiaogq  Sep 6, 2014 10:41:55 AM
         */
        private void deleteRecord() {
            Integer hostId = relatedRecord.parse().getHostTable().getEntityId();
            Integer relatedId = relatedRecord.parse().getRelatedTable().getEntityId();
            Integer serial = relatedRecord.getSerial();
            //删除相关项注册表中的数据(sys_related_config)
            StringBuffer sql = new StringBuffer("delete from sys_related_config where host_id = "+hostId);
            sql.append(" and related_id = "+relatedId+" and serial= "+serial);
            jdbcTemplate.execute(sql.toString());
        }

        /**
         * 删除相关项表
         * @author xiagqo  2016-09-02
         */
        private void deleteTable() {
            StringBuffer sql = new StringBuffer("drop table if exists "+relatedRecord.getRelatedTableName());
            jdbcTemplate.execute(sql.toString());
        }

        public void rollbackDbException() {
            // 恢复删除的表
            new RelatedAdd().createRelatedTable();
        }

    }

}
